﻿@{
    ViewBag.Title = "数据库同步";
}
@section header{
    <style>
    </style>
}
<div class="ui-layout">
    <table>
        <tr>
            <td>
                <div id="datasource">
                    <h2 style="text-align:center;">数据源</h2>
                    <hr class="layui-bg-green">
                    <form class="" id="addForm" style="height:100%">
                        <div style="margin-top: 10px; margin-left: 10px; margin-right: 10px;">
                            <div style="padding-top: 10px; margin-right: 30px;">
                                <div class="panel-body" style="width: 100%;">
                                    <table class="form" border="0">
                                        <tr>
                                            <th class="formTitle" style="width:100px;">数据库版本：</th>
                                            <td class="formValue">
                                                <select id="SQL_version" name="SQL_version" class="form-control required" style="width:308px;">
                                                    <option value="010">SQL Server 2012</option>
                                                    <option value="021">SQL Server 2008</option>
                                                    <option value="0571">SQL Server 2002</option>
                                                </select>
                                            </td>
                                        </tr>
                                        <tr>
                                            <th class="formTitle">服务器地址：</th>
                                            <td class="formValue">
                                                <input id="Server" name="Server" type="text" value="127.0.0.1" onchange="getDataBase('post')" class="form-control required" style="width:300px;" />
                                            </td>
                                        </tr>
                                        <tr>
                                            <th class="formTitle">用户名：</th>
                                            <td class="formValue">
                                                <input id="UserName" name="UserName" type="text" value="sa" onchange="getDataBase('post')" class="form-control required" style="width:300px;" />
                                            </td>
                                        </tr>
                                        <tr>
                                            <th class="formTitle">密码：</th>
                                            <td class="formValue">
                                                <input id="Password" name="Password" type="password" value="123456" onchange="getDataBase('post')" class="form-control required" style="width:300px;" />
                                            </td>
                                        </tr>
                                        <tr>
                                            <th class="formTitle">数据库：</th>
                                            <td class="formValue">
                                                <select id="DataBase" name="DataBase" onchange="getTable('post')" class="form-control required" style="width:308px;"></select>
                                            </td>
                                        </tr>
                                        <tr>
                                            <th class="formTitle">表：</th>
                                            <td class="formValue">
                                                <select id="Table" name="Table" onchange="getTerm()" class="form-control required" style="width:308px;"></select>
                                            </td>
                                        </tr>
                                    </table>
                                </div>
                            </div>
                        </div>
                    </form>
                </div>
            </td>
            <td>
                <div id="datarecive">
                    <h2 style="text-align:center;">接收库</h2>
                    <hr class="layui-bg-green">
                    <form class="" id="addForm1" style="height:100%">
                        <div style="margin-top: 10px; margin-left: 10px; margin-right: 10px;">
                            <div style="padding-top: 10px; margin-right: 30px;">
                                <div class="panel-body" style="width: 100%;">
                                    <table class="form" border="0">
                                        <tr>
                                            <th class="formTitle" style="width:100px;">数据库版本：</th>
                                            <td class="formValue">
                                                <select id="SQL_version1" name="SQL_version1" class="form-control required" style="width:308px;">
                                                    <option value="010">SQL Server 2012</option>
                                                    <option value="021">SQL Server 2008</option>
                                                    <option value="0571">SQL Server 2002</option>
                                                </select>
                                            </td>
                                        </tr>
                                        <tr>
                                            <th class="formTitle">服务器地址：</th>
                                            <td class="formValue">
                                                <input id="Server1" name="Server1" type="text" onchange="getDataBase('get')" class="form-control required" style="width:300px;" />
                                            </td>
                                        </tr>
                                        <tr>
                                            <th class="formTitle">用户名：</th>
                                            <td class="formValue">
                                                <input id="UserName1" name="UserName1" type="text" onchange="getDataBase('get')" class="form-control required" style="width:300px;" />
                                            </td>
                                        </tr>
                                        <tr>
                                            <th class="formTitle">密码：</th>
                                            <td class="formValue">
                                                <input id="Password1" name="Password1" type="password" onchange="getDataBase('get')" class="form-control required" style="width:300px;" />
                                            </td>
                                        </tr>
                                        <tr>
                                            <th class="formTitle">数据库：</th>
                                            <td class="formValue">
                                                <select id="DataBase1" name="DataBase1" onchange="getTable('get')" class="form-control required" style="width:308px;"></select>
                                            </td>
                                        </tr>
                                        <tr>
                                            <th class="formTitle">表：</th>
                                            <td class="formValue">
                                                <select id="Table1" name="Table1" onchange="getReciveColumn()" class="form-control required" style="width:308px;"></select>
                                            </td>
                                        </tr>
                                    </table>
                                </div>
                            </div>
                        </div>
                    </form>
                </div>
            </td>
        </tr>
    </table>
    <table id="orderTable" style="width:600px;margin:auto;left:50%;top:20%">
        <thead>
            <tr>
                <th>条件</th>
                <th>操作</th>
            </tr>
        </thead>
        <tr id="row0">
            <td>
                <select id="UrbanDepNo" name="UrbanDepNo" style="width:45%;display:inline;"></select>=<input type="text" id="EndRoadName0" name="EndRoadName" style="width:45%" />
            </td>
            <td>
                <input type="button" name="delete" value="删 除" style="width:80px" onclick="deleteSelectedRow('0')" />
            </td>
        </tr>
        <tr>
            <td align="center" colspan="5">
                <br />
                <input type="button" name="insert" value="增加一行" style="width:80px" onclick="insertNewRow()" />&nbsp&nbsp
                <input type="button" value=" 保    存 " style="width:80px" onclick="GetValue()" />
            </td>
        </tr>
    </table>
    <table id="orderTable1" style="width:600px;margin:auto;left:50%;top:20%">
        <thead>
            <tr>
                <th>字段</th>
                <th>操作</th>
            </tr>
        </thead>
        <tr id="columnrow">
            <td>
                <select id="SourceColumn" name="SourceColumn" style="width:45%;display:inline;"></select>——<select id="ReciveColumn" name="ReciveColumn" style="width:45%;"></select>
            </td>
            <td>
                <input type="button" name="delete" value="删 除" style="width:80px" onclick="deleteSelectedRow(0)" />
            </td>
        </tr>
        <tr>
            <td align="center" colspan="5">
                <br />
                <input type="button" name="insert" value="增加一行" style="width:80px" onclick="insertNewRow1()" />&nbsp&nbsp
            </td>
        </tr>
    </table>

    <div class="layui-row" style="margin-top:30px; min-height:50px;text-align:center">
        <button class="layui-btn layui-btn-primary" onclick="UpdateTable1()">替换</button>
        <button class="layui-btn layui-btn-primary" onclick="InsertToData()">追加</button>
        <button class=" layui-btn layui-btn-primary" onclick="UpdateTable()">更新</button>
    </div>
    <div class="layui-row" style="margin-top:20px;min-height:100px;">
        <h4 style="text-align:center;margin-top:10px">SQL语句</h4>
        <p id="sqlText" style="color:red"></p>
    </div>
</div>
@section scripts{
    <script>
        var baseUrl = "/Sys/DataBaseTransport/";
        $(function () {

        })
        //声明全局变量
        var formvalue = "";
        var flag = 1;
        var flags = 1;
        var index = 1;
        var firstCell = "";
        var nextCell = "";
        $(function () {
            //初始化第一行
            firstCell = $("#row0 td:eq(0)").html();
            nextCell = $("#row1 td:eq(0)").html();
        });

        //-----------------新增一行-----------start---------------
        function insertNewRow() {
            //获取表格有多少行
            var rowLength = $("#orderTable tr").length;
            //这里的rowId就是row加上标志位的组合。是每新增一行的tr的id。
            var rowId = "row" + flag;

            //每次往下标为flag+1的下面添加tr,因为append是往标签内追加。所以用after
            var insertStr = "<tr id=" + rowId + ">"
                + "<td>" + firstCell + "</td>"
                + "<td><input type='button' name='delete' value='删除' style='width:80px' onclick='deleteSelectedRow(\"" + rowId + "\")' />"
                + "</tr>";
            //这里的行数减2，是因为要减去底部的一行和顶部的一行，剩下的为开始要插入行的索引
            $("#orderTable tr:eq(" + (rowLength - 2) + ")").after(insertStr); //将新拼接的一行插入到当前行的下面
            //为新添加的行里面的控件添加新的id属性。
            $("#" + rowId + " td:eq(0)").children().eq(0).attr("id", "StStartRoad" + flag);
            $("#" + rowId + " td:eq(0)").children().eq(1).attr("id", "EndRoad" + flag);
            //每插入一行，flag自增一次
            flag++;
        }
        function insertNewRow1() {
            //获取表格有多少行
            var rowLength = $("#orderTable1 tr").length;
            //这里的rowId就是row加上标志位的组合。是每新增一行的tr的id。
            var rowId = "columnrow" + flags;

            //每次往下标为flag+1的下面添加tr,因为append是往标签内追加。所以用after
            var insertStr = "<tr id=" + rowId + ">"
                + "<td>" + nextCell + "</td>"
                + "<td><input type='button' name='delete' value='删除' style='width:80px' onclick='deleteSelectedRow1(\"" + rowId + "\")' />"
                + "</tr>";
            //这里的行数减2，是因为要减去底部的一行和顶部的一行，剩下的为开始要插入行的索引
            $("#orderTable1 tr:eq(" + (rowLength - 2) + ")").after(insertStr); //将新拼接的一行插入到当前行的下面
            //为新添加的行里面的控件添加新的id属性。
            $("#" + rowId + " td:eq(0)").children().eq(0).attr("id", "SourceColumn" + flags);
            $("#" + rowId + " td:eq(0)").children().eq(1).attr("id", "ReciveColumn" + flags);
            //每插入一行，flag自增一次
            flags++;
        }

        //-----------------删除一行，根据行ID删除-start--------
        function deleteSelectedRow(rowID) {
            if (confirm("确定删除该行吗？")) {
                $("#" + rowID).remove();
                flag--;
            }
        }
        function deleteSelectedRow1(rowID) {
            if (confirm("确定删除该行吗？")) {
                $("#" + rowID).remove();
                flags--;
            }
        }
        function getDataBase(type) {
            if (type == 'post') {
                if (!!$("#Server").val() && !!$("#UserName").val() && !!$("#Password").val()) {
                    $.ajax({
                        url: baseUrl + "GetDataBase?server=" + $("#Server").val() + "&username=" + $("#UserName").val() + "&password=" + $("#Password").val(),
                        type: "post",
                        dataType: "json",
                        async: "false",
                        success: function (data) {
                            console.log("成功");
                            $.each(data, function (index, item) {
                                $('#DataBase').append(new Option(item.text, item.id));// 下拉菜单里添加元素
                            });
                        }
                    })
                }
            }
            else if (type = 'get') {
                if (!!$("#Server1").val() && !!$("#UserName1").val() && !!$("#Password1").val()) {
                    $.ajax({
                        url: baseUrl + "GetDataBase?server=" + $("#Server1").val() + "&username=" + $("#UserName1").val() + "&password=" + $("#Password1").val(),
                        type: "post",
                        dataType: "json",
                        async: "false",
                        success: function (data) {
                            console.log("成功");
                            $.each(data, function (index, item) {
                                $('#DataBase1').append(new Option(item.text, item.id));// 下拉菜单里添加元素
                            });
                        }
                    })
                }
            }
        }
        function getTable(type) {
            if (type == 'post') {
                var db = $("#DataBase option:selected").val();
                if (!!db) {
                    $.ajax({
                        url: baseUrl + "GetTable?server=" + $("#Server").val() + "&username=" + $("#UserName").val() + "&password=" + $("#Password").val() + "&database=" + db,
                        type: "post",
                        dataType: "json",
                        async: "false",
                        success: function (data) {
                            $('#Table option').remove();
                            console.log("成功");
                            $.each(data, function (index, item) {
                                $('#Table').append(new Option(item.text, item.id));// 下拉菜单里添加元素
                            });
                        }
                    })
                }
            }
            else if (type == 'get') {
                var db = $("#DataBase1 option:selected").val();
                if (!!db) {
                    $.ajax({
                        url: baseUrl + "GetTable?server=" + $("#Server1").val() + "&username=" + $("#UserName1").val() + "&password=" + $("#Password1").val() + "&database=" + db,
                        type: "post",
                        dataType: "json",
                        async: "false",
                        success: function (data) {
                            console.log("成功");
                            $("#Table1 option").remove();
                            $.each(data, function (index, item) {
                                $('#Table1').append(new Option(item.text, item.id));// 下拉菜单里添加元素
                            });
                        }
                    })
                }
            }
        }
        function getTerm() {
            if (!!$("#Table option:selected").val()) {
                $.ajax({
                    url: baseUrl + "GetColumn?server=" + $("#Server").val() + "&username=" + $("#UserName").val() + "&password=" + $("#Password").val() + "&database=" + $("#DataBase option:selected").val() + "&table=" + $("#Table option:selected").val(),
                    type: "post",
                    dataType: "json",
                    async: "false",
                    success: function (data) {
                        console.log("成功");
                        $('#UrbanDepNo option').remove();
                        $('#SourceColumn option').remove();
                        $.each(data, function (index, item) {
                            $('#UrbanDepNo').append(new Option(item.text, item.id));
                            if (item.text != 'id') {
                                $('#SourceColumn').append(new Option(item.text, item.id));
                            }

                        });
                        firstCell = $("#row0 td:eq(0)").html();
                        nextCell = $("#columnrow td:eq(0)").html();
                    }
                })
            }
        }
        function getReciveColumn() {
            if (!!$("#Table1 option:selected").val()) {
                $.ajax({
                    url: baseUrl + "GetColumn?server=" + $("#Server1").val() + "&username=" + $("#UserName1").val() + "&password=" + $("#Password1").val() + "&database=" + $("#DataBase1 option:selected").val() + "&table=" + $("#Table1 option:selected").val(),
                    type: "post",
                    dataType: "json",
                    async: "false",
                    success: function (data) {
                        console.log("成功");
                        $('#ReciveColumn option').remove();
                        $.each(data, function (index, item) {
                            if (item.text != 'id') {
                                $('#ReciveColumn').append(new Option(item.text, item.id));
                            }

                        });
                        nextCell = $("#columnrow td:eq(0)").html();
                    }
                })
            }
        }

        function InsertToData() {
            //字段拼接
            var values = $("#SourceColumn option:selected").val();
            var fields = $("#ReciveColumn option:selected").val();
            var where = $("#UrbanDepNo option:selected").val() + "='" + $("#EndRoadName0").val() + "'";
            for (i = 1; i < flags; i++) {
                values += "," + $("#SourceColumn" + i + " option:selected").val();
                fields += "," + $("#ReciveColumn" + i + " option:selected").val();
            }
            for (i = 1; i < flag; i++) {
                where += " and " + $("#StStartRoad" + i + " option:selected").val() + "='" + $("#EndRoad" + i).val() + "'";
            }
            var sql = "insert into " + $("#DataBase1 option:selected").val() + ".dbo." + $("#Table1 option:selected").val() +
                "(" + fields + ") select " + values + " from " + $("#DataBase option:selected").val() + ".dbo." +
                $("#Table option:selected").val() + " where " + where;
            console.log(fields + "--" + values);
            console.log(sql);
            var sqlText = document.getElementById("sqlText");
            sqlText.innerHTML = sql;
            $.ajax({
                url: baseUrl + "InsertInto?server=" + $("#Server").val() + "&username=" + $("#UserName").val() + "&password=" + $("#Password").val() + "&database=" + $("#DataBase option:selected").val() + "&sql=" + sql,
                type: "post",
                dataType: "json",
                async: "false",
                success: function (result) {
                    if (result.flag > 0) {
                        $.modalMsg("数据追加成功!", "success");
                    } else {
                        $.modalMsg("数据追加失败!", "error");
                    }
                }
            })
        }
        function UpdateTable() {
            var fields = $("#ReciveColumn option:selected").val() + "=a." + $("#SourceColumn option:selected").val();
            var where = "a." + $("#UrbanDepNo option:selected").val() + "='" + $("#EndRoadName0").val() + "'";
            for (i = 1; i < flags; i++) {
                fields += "," + $("#ReciveColumn" + i + " option:selected").val() + "=a." + $("#SourceColumn" + i + " option:selected").val();
            }
            for (i = 1; i < flag; i++) {
                where += " and a." + $("#StStartRoad" + i + " option:selected").val() + "='" + $("#EndRoad" + i).val() + "'";
            }
            var sql = " update " + $("#DataBase1 option:selected").val() + ".dbo." + $("#Table1 option:selected").val() + " set " + fields + " from " + $("#Table option:selected").val() + " a where " + where;
            var sqlText = document.getElementById("sqlText");
            sqlText.innerHTML = sql;
            $.ajax({
                url: baseUrl + "UpdateTable?server=" + $("#Server").val() + "&username=" + $("#UserName").val() + "&password=" + $("#Password").val() + "&database=" + $("#DataBase option:selected").val() + "&sql=" + sql,
                type: "post",
                dataType: "json",
                async: "false",
                success: function (result) {
                    if (result.flag > 0) {
                        $.modalMsg("数据更新成功!", "success");
                    } else {
                        $.modalMsg("数据更新失败!", "error");
                    }
                }
            })

        }
        function UpdateTable1() {
            var fields = $("#ReciveColumn option:selected").val() + "=a." + $("#SourceColumn option:selected").val();
            var where = "a." + $("#UrbanDepNo option:selected").val() + "='" + $("#EndRoadName0").val() + "'";
            for (i = 1; i < flags; i++) {
                fields += "," + $("#ReciveColumn" + i + " option:selected").val() + "=a." + $("#SourceColumn" + i + " option:selected").val();
            }
            for (i = 1; i < flag; i++) {
                where += " and a." + $("#StStartRoad" + i + " option:selected").val() + "='" + $("#EndRoad" + i).val() + "'";
            }
            var sql = " update " + $("#DataBase1 option:selected").val() + ".dbo." + $("#Table1 option:selected").val() + " set " + fields + " from " + $("#Table option:selected").val() + " a where " + where;
            var sqlText = document.getElementById("sqlText");
            sqlText.innerHTML = sql;
            $.ajax({
                url: baseUrl + "UpdateTable?server=" + $("#Server").val() + "&username=" + $("#UserName").val() + "&password=" + $("#Password").val() + "&database=" + $("#DataBase option:selected").val() + "&sql=" + sql,
                type: "post",
                dataType: "json",
                async: "false",
                success: function (result) {
                    if (result.flag > 0) {
                        $.modalMsg("数据替换成功!", "success");
                    } else {
                        $.modalMsg("数据替换失败!", "error");
                    }
                }
            })

        }
    </script>
}